import datetime

import boto3
from athena_submit import submit

athena = boto3.client('athena', region_name="cn-northwest-1", aws_access_key_id="AKIAWTBG3E42IMYODWHL",
                      aws_secret_access_key="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")
glue = boto3.client('glue', region_name="cn-northwest-1", aws_access_key_id="AKIAWTBG3E42IMYODWHL",
                    aws_secret_access_key="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")


def run():

    # parquet表
    tables_parquet = {
        'original.fridge_repair_order': ['p_date'],
        'ods.fridge_repair_order': ['p_date'],
        'ods.iot_fridge': ['p_date', 'material'],
        'ods.iot_fridge_mqtt': ['p_date', 'material'],
        'ods.kyd_ods': ['p_date'],
        'ods.device': ['material'],
        'dim.material': [],
        'dim.output': []
    }

    #csv表
    tables_csv = {
        'original.fridge_kyd_all': [],
        'original.kyd_all': []
    }


    # 添加以fridge开头的表
    r1 = glue.get_tables(
        DatabaseName='dws',
        MaxResults=300
    )
    for one in r1['TableList']:
        if str(one['Name']).startswith('fridge') or str(one['Name']).startswith('freezer'):
            pks = []
            for pk in one['PartitionKeys']:
                pks.append(pk['Name'])
            tables_parquet[one['DatabaseName'] + '.' + one['Name']] = pks

    r1 = glue.get_tables(
        DatabaseName='ods',
        MaxResults=300
    )
    for one in r1['TableList']:
        if str(one['Name']).startswith('fridge') or str(one['Name']).startswith('freezer'):
            pks = []
            for pk in one['PartitionKeys']:
                pks.append(pk['Name'])
            tables_parquet[one['DatabaseName'] + '.' + one['Name']] = pks

    r1 = glue.get_tables(
        DatabaseName='ads',
        MaxResults=300
    )
    for one in r1['TableList']:
        if str(one['Name']).startswith('fridge') or str(one['Name']).startswith('freezer'):
            pks = []
            for pk in one['PartitionKeys']:
                pks.append(pk['Name'])
            tables_parquet[one['DatabaseName'] + '.' + one['Name']] = pks
    r1 = glue.get_tables(
        DatabaseName='dim',
        MaxResults=300
    )
    for one in r1['TableList']:
        if str(one['Name']).startswith('fridge') or str(one['Name']).startswith('freezer'):
            pks = []
            for pk in one['PartitionKeys']:
                pks.append(pk['Name'])
            tables_parquet[one['DatabaseName'] + '.' + one['Name']] = pks

    print(tables_parquet)
    map = {"parquet": tables_parquet, "csv": tables_csv}

    # 执行创建表sql
    for format_type, tables in map.items():
        for table, parts in tables.items():
            db, table_name = table.split('.')
            if db == "dim":
                where_clause = ""
            else:
                where_clause = " where false "

            submit(f"drop table if exists test_group.{db}_{table_name}")
            if len(parts) > 0:
                s = "','".join(parts)
                part_str = f" ,partitioned_by = ARRAY['{s}'] "
            else:
                part_str = ''
            sql = f"""
                create table test_group.{db}_{table_name} WITH (
                  external_location = 's3://testgroup-data/{db}/{table_name}',
                  format = '{format_type}' {part_str}
                 )
                as select * from
                {table}
                {where_clause}
                """
            print(sql)
            submit(sql)
            if format_type == "csv":
                sql = f"""
                        alter table test_group.{db}_{table_name} SET TBLPROPERTIES ('skip.header.line.count' = '1' )
                    """
                submit(sql)
            print("\r")


    # 测试环境的iot原始表，使用与ods表相同的csv结构
    tables_special = {
        'original.fridge_http_hbase': ['p_date', 'material'],
        'original.fridge_mqtt_hbase': ['p_date', 'material']
    }
    submit("drop table if exists test_group.original_fridge_http_hbase")
    sql2 = f"""
                CREATE EXTERNAL TABLE `original_fridge_http_hbase`(
                  `sn` string COMMENT '', 
                  `isnew` string COMMENT '', 
                  `model` string COMMENT '', 
                  `submodel` string COMMENT '', 
                  `city` string COMMENT '', 
                  `starttime` string COMMENT '', 
                  `powerontime` string COMMENT '', 
                  `timestamp` string COMMENT '', 
                  `username` string COMMENT '', 
                  `phone` string COMMENT '', 
                  `frequency` string COMMENT '', 
                  `jiegou` string COMMENT '', 
                  `lqstate` string COMMENT '', 
                  `rongji` string COMMENT '', 
                  `category` string COMMENT '', 
                  `freezedoor` string COMMENT '', 
                  `freezedoora` string COMMENT '', 
                  `freezedoorb` string COMMENT '', 
                  `bwoff` string COMMENT '', 
                  `bwoffdetime` string COMMENT '', 
                  `changingdoor` string COMMENT '', 
                  `changingfanstate` string COMMENT '', 
                  `changingheat` string COMMENT '', 
                  `changingroomairdoorstatus` string COMMENT '', 
                  `changingroomchange` string COMMENT '', 
                  `changingroomevaporationsensortempreture` string COMMENT '', 
                  `changingroomfanstatus` string COMMENT '', 
                  `changingroomtargettempreture` string COMMENT '', 
                  `changingsensortempreture` string COMMENT '', 
                  `changingfanheat` string COMMENT '', 
                  `childrenlock` string COMMENT '', 
                  `colddoor` string COMMENT '', 
                  `coldoff` string COMMENT '', 
                  `coldoffdetime` string COMMENT '', 
                  `coldroomairdoorstatus` string COMMENT '', 
                  `coldroomchange` string COMMENT '', 
                  `coldroomevaporationsensortempreture` string COMMENT '', 
                  `coldroomfanstate` string COMMENT '', 
                  `coldroomfanstatus` string COMMENT '', 
                  `coldroomsensortempreture` string COMMENT '', 
                  `coldroomtargettempreture` string COMMENT '', 
                  `commandnull` string COMMENT '', 
                  `compressorstatus` string COMMENT '', 
                  `condensationheat` string COMMENT '', 
                  `coldroomairdoorheat` string COMMENT '', 
                  `condensefanstate` string COMMENT '', 
                  `condenserfanstatus` string COMMENT '', 
                  `dangwei` string COMMENT '', 
                  `denba` string COMMENT '', 
                  `denbatime` string COMMENT '', 
                  `displayboardstatus` string COMMENT '', 
                  `electricswitch1` string COMMENT '', 
                  `electricswitch2` string COMMENT '', 
                  `electricswitch3` string COMMENT '', 
                  `electromagnetic1` string COMMENT '', 
                  `electromagnetic2` string COMMENT '', 
                  `evnsensortempreture` string COMMENT '', 
                  `fastcold` string COMMENT '', 
                  `fastcolddetime` string COMMENT '', 
                  `fastfreeze` string COMMENT '', 
                  `fastfreezedetime` string COMMENT '', 
                  `freezeheat` string COMMENT '', 
                  `freezeroomchange` string COMMENT '', 
                  `freezeroomevaporationsensortempreture` string COMMENT '', 
                  `freezeroomfanstate` string COMMENT '', 
                  `freezeroomfanstatus` string COMMENT '', 
                  `freezeroomsensortempreture` string COMMENT '', 
                  `freezeroomtargettempreture` string COMMENT '', 
                  `gardenstuff` string COMMENT '', 
                  `holiday` string COMMENT '', 
                  `holidaydetime` string COMMENT '', 
                  `humiditysensorvalue` string COMMENT '', 
                  `iced` string COMMENT '', 
                  `infraredswitch` string COMMENT '', 
                  `lcchange` string COMMENT '', 
                  `ldhange` string COMMENT '', 
                  `ldspeed` string COMMENT '', 
                  `leco` string COMMENT '', 
                  `lecodetime` string COMMENT '', 
                  `mastringenanceend` string COMMENT '', 
                  `powerremind` string COMMENT '', 
                  `smart` string COMMENT '', 
                  `smartdetime` string COMMENT '', 
                  `temperaturecompensationheat` string COMMENT '', 
                  `updateversion` string COMMENT '', 
                  `uvlightstatus` string COMMENT '', 
                  `wifiswitch` string COMMENT '', 
                  `ultraviolet` string COMMENT '', 
                  `zerofresh` string COMMENT '', 
                  `faultd01` string COMMENT '', 
                  `faultd02` string COMMENT '', 
                  `faultd03` string COMMENT '', 
                  `faultd04` string COMMENT '', 
                  `faultf02` string COMMENT '', 
                  `faultf03` string COMMENT '', 
                  `faultf04` string COMMENT '', 
                  `faultf05` string COMMENT '', 
                  `faultp01` string COMMENT '', 
                  `faultp02` string COMMENT '', 
                  `faults01` string COMMENT '', 
                  `faults02` string COMMENT '', 
                  `faults03` string COMMENT '', 
                  `faults04` string COMMENT '', 
                  `faults05` string COMMENT '', 
                  `faults06` string COMMENT '', 
                  `faults07` string COMMENT '', 
                  `source` string COMMENT '', 
                  `faults08` string COMMENT '',
                   `p_date` string COMMENT '', 
                  `material` string COMMENT '')
              ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
                WITH SERDEPROPERTIES (
                   'separatorChar' = ',',
                   'quoteChar' = '"',
                   'escapeChar' = '\\'
                )
                LOCATION
                  's3://testgroup-data/original/fridge_http_hbase'
                TBLPROPERTIES ('skip.header.line.count'='1' )
                """
    submit(sql2)

    submit("drop table if exists test_group.original_fridge_mqtt_hbase")
    sql3 = f"""
        CREATE EXTERNAL TABLE `original_fridge_mqtt_hbase`(
              `sn` string COMMENT 'from deserializer', 
              `model` string COMMENT 'from deserializer', 
              `source` string COMMENT 'from deserializer', 
              `timestamp` string COMMENT 'from deserializer', 
              `bwoff` string COMMENT 'from deserializer', 
              `changingdoor` string COMMENT 'from deserializer', 
              `changingfanvalue` string COMMENT 'from deserializer', 
              `changingheat` string COMMENT 'from deserializer', 
              `changingroomairdoorheat` string COMMENT 'from deserializer', 
              `changingroomairdoorstatus` string COMMENT 'from deserializer', 
              `changingroomevaporator` string COMMENT 'from deserializer', 
              `changingroomevaporationsensortempreture` string COMMENT 'from deserializer', 
              `changingroomfanstatus` string COMMENT 'from deserializer', 
              `changingroomtargettempretureinteger` string COMMENT 'from deserializer', 
              `changingroomtargettempreturedecimal` string COMMENT 'from deserializer', 
              `changingsensortempreture` string COMMENT 'from deserializer', 
              `changingsensortempreturedecimal` string COMMENT 'from deserializer', 
              `changingsensortempretureinteger` string COMMENT 'from deserializer', 
              `childrenlock` string COMMENT 'from deserializer', 
              `colddoor` string COMMENT 'from deserializer', 
              `coldfanvalue` string COMMENT 'from deserializer', 
              `coldoff` string COMMENT 'from deserializer', 
              `condenserfanstatus` string COMMENT 'from deserializer', 
              `cookedfood` string COMMENT 'from deserializer', 
              `coldroomairdoorheat` string COMMENT 'from deserializer', 
              `coldroomairdoorstatus` string COMMENT 'from deserializer', 
              `coldroomevaporator` string COMMENT 'from deserializer', 
              `coldroomevaporationsensortempreture` string COMMENT 'from deserializer', 
              `coldroomfanstatus` string COMMENT 'from deserializer', 
              `coldroomsensortempreture` string COMMENT 'from deserializer', 
              `coldroomsensortempreturedecimal` string COMMENT 'from deserializer', 
              `coldroomsensortempretureinteger` string COMMENT 'from deserializer', 
              `coldroomtargettempreture` string COMMENT 'from deserializer', 
              `coldroomtargettempreturedecimal` string COMMENT 'from deserializer', 
              `coldroomtargettempretureinteger` string COMMENT 'from deserializer', 
              `compressorspeed` string COMMENT 'from deserializer', 
              `compressorstatus` string COMMENT 'from deserializer', 
              `condensationfanvalue` string COMMENT 'from deserializer', 
              `condensationheat` string COMMENT 'from deserializer', 
              `displayboardstatus` string COMMENT 'from deserializer', 
              `deepfastfreeze` string COMMENT 'from deserializer', 
              `evnsensortempreture` string COMMENT 'from deserializer', 
              `evnsensortempreturedecimal` string COMMENT 'from deserializer', 
              `evnsensortempretureinteger` string COMMENT 'from deserializer', 
              `fastcold` string COMMENT 'from deserializer', 
              `fastfreeze` string COMMENT 'from deserializer', 
              `fastfrozen` string COMMENT 'from deserializer', 
              `freezedoora` string COMMENT 'from deserializer', 
              `freezedoorb` string COMMENT 'from deserializer', 
              `freezefanvalue` string COMMENT 'from deserializer', 
              `freezeheat` string COMMENT 'from deserializer', 
              `freezeroomevaporationsensortempreture` string COMMENT 'from deserializer', 
              `freezeroomevaporator` string COMMENT 'from deserializer', 
              `freezeroomfanstatus` string COMMENT 'from deserializer', 
              `freezeroomsensortempreture` string COMMENT 'from deserializer', 
              `freezeroomsensortempreturedecimal` string COMMENT 'from deserializer', 
              `freezeroomsensortempretureinteger` string COMMENT 'from deserializer', 
              `freezeroomtargettempreture` string COMMENT 'from deserializer', 
              `freezeroomtargettempretureindecimal` string COMMENT 'from deserializer', 
              `freezeroomtargettempretureinteger` string COMMENT 'from deserializer', 
              `gardenstuff` string COMMENT 'from deserializer', 
              `holiday` string COMMENT 'from deserializer', 
              `humiditysensorvalue` string COMMENT 'from deserializer', 
              `iced` string COMMENT 'from deserializer', 
              `internaltime` string COMMENT 'from deserializer', 
              `leco` string COMMENT 'from deserializer', 
              `maintenanceend` string COMMENT 'from deserializer', 
              `mark` string COMMENT 'from deserializer', 
              `mominfant` string COMMENT 'from deserializer', 
              `powerremind` string COMMENT 'from deserializer', 
              `remaintimeforshortquickfreez` string COMMENT 'from deserializer', 
              `shortquickfreez` string COMMENT 'from deserializer', 
              `speechswitch` string COMMENT 'from deserializer', 
              `smart` string COMMENT 'from deserializer', 
              `timeforfastfrozen` string COMMENT 'from deserializer', 
              `wifireset` string COMMENT 'from deserializer', 
              `wifiswitch` string COMMENT 'from deserializer', 
              `zerofresh` string COMMENT 'from deserializer', 
              `passthrough38` string COMMENT 'from deserializer', 
              `consttemperatureandmicrofreez` string COMMENT 'from deserializer', 
              `controlsource` string COMMENT 'from deserializer', 
              `zerodegree` string COMMENT 'from deserializer', 
              `icecold` string COMMENT 'from deserializer', 
              `wifisoftswitch` string COMMENT 'from deserializer', 
              `foodfreshness` string COMMENT 'from deserializer', 
              `extenddata` string COMMENT 'from deserializer', 
              `changingclose` string COMMENT 'from deserializer', 
              `changingroomtargettempreture` string COMMENT 'from deserializer', 
              `condensationheater` string COMMENT 'from deserializer', 
              `remaintimefornoisereduction` string COMMENT 'from deserializer', 
              `iced_gardenstuff` string COMMENT 'from deserializer', 
              `zerofresh_cookedfood` string COMMENT 'from deserializer', 
              `freshfruit_mominfant` string COMMENT 'from deserializer', 
              `defrost` string COMMENT 'from deserializer',
               `p_date` string, 
              `material` string)
            ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
                WITH SERDEPROPERTIES (
                   'separatorChar' = ',',
                   'quoteChar' = '"',
                   'escapeChar' = '\\'
                )
            LOCATION
              's3://testgroup-data/original/fridge_mqtt_hbase'
            TBLPROPERTIES ('skip.header.line.count'='1' )
        """
    submit(sql3)

    print("done")


    # for table in tables_parquet:
    #     table = table.replace(' ','')
    #     sql1 = f''' create table  test.{table}_b as select * from test.{table} '''
    #     submit(sql1)
    #     sql2 = f'drop table test.{table}'
    #     submit(sql2)
    #     sql3 = f'''
    #             create table  test.{table} WITH (
    #                   external_location = 's3://athena-testdata/{table}',
    #                   format = 'PARQUET')
    #             as select * from
    #             test.{table}_b;
    #            '''
    #     submit(sql3)
    #     sql4 = f"drop table test.{table}_b"
    #     submit(sql4)
    #     print("done:",table)


if __name__ == '__main__':
    run()
